package com.demo.apparel.mapper.uniapp;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.apparel.entity.Clothes;
import com.demo.apparel.vo.ClothesSimpleVO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface WxClothesMapper extends BaseMapper<Clothes> {

    @Insert("""
        INSERT INTO clothes (id, user_id, image_url, update_time, status_sale)
        VALUES (#{id}, #{userId}, #{imageUrl}, #{updateTime}, #{statusSale})
    """)
    int insert(Clothes clothes);

    @Update("""
        UPDATE clothes SET
            name = #{name},
            description = #{description},
            category = #{category},
            season = #{season},
            update_time = #{updateTime}
        WHERE id = #{id}
    """)
    int updateById(Clothes clothes);

    @Select("SELECT * FROM clothes WHERE id = #{id} LIMIT 1")
    Clothes selectById(@Param("id") String id);


    /**
     *
     * 获取全部所有用户的数据
     */

    // 查询所有子类 name（去重）+ 每个 name 下的衣服
    @Select("""
        SELECT DISTINCT name FROM clothes WHERE category = #{category}
    """)
    List<String> selectSubNamesByCategory(@Param("category") int category);


@Select("""
    SELECT c.id, c.description, c.image_url AS image,
           t.type_name AS typeName, t.keywords, c.season
    FROM clothes c
    LEFT JOIN clothes_type t ON c.name = t.type_name AND c.category = t.category
    WHERE c.category = #{category} AND c.name = #{name}
""")
List<ClothesSimpleVO> selectClothesByCategoryAndName(@Param("category") int category, @Param("name") String name);

    @Select("""
    SELECT c.id, c.description, c.image_url AS image,
           t.type_name AS typeName, t.keywords, c.season
    FROM clothes c
    LEFT JOIN clothes_type t ON c.name = t.type_name AND c.category = t.category
    WHERE c.category = #{category}
""")
List<ClothesSimpleVO> selectAllClothesByCategory(@Param("category") int category);


    @Select("""
        SELECT COUNT(*) FROM clothes WHERE category = #{category} AND name = #{name}
    """)
    Integer countClothesByCategoryAndName(@Param("category") int category, @Param("name") String name);

    @Select("""
        SELECT COUNT(*) FROM clothes WHERE category = #{category}
    """)
    Integer countAllByCategory(@Param("category") int category);


    /**
     * 获取当前的用户的数据
     */
    // 查询该用户在指定分类下的所有衣服
    @Select("SELECT * FROM clothes WHERE user_id = #{userId} AND category = #{category}")
    List<Clothes> selectAllByCategory(@Param("userId") String userId, @Param("category") Integer category);

    // 查询子类名称（即 name 字段）去重
    @Select("SELECT DISTINCT name FROM clothes WHERE user_id = #{userId} AND category = #{category}")
    List<String> selectDistinctSubNames(@Param("userId") String userId, @Param("category") Integer category);

    // 查询具体子类（分类 + name）
    @Select("SELECT * FROM clothes WHERE user_id = #{userId} AND category = #{category} AND name = #{name}")
    List<Clothes> selectByCategoryAndSubName(@Param("userId") String userId, @Param("category") Integer category, @Param("name") String name);

    /**
     * 实现搜索功能get
     */
    @Select("""
    <script>
    SELECT id, description, image_url AS imageUrl
    FROM clothes
    WHERE user_id = #{userId}
      AND (name LIKE CONCAT('%', #{keyword}, '%')
           OR description LIKE CONCAT('%', #{keyword}, '%'))
      <if test="season != null and season != ''">
        AND season = #{season}
      </if>
    </script>
""")
    List<Clothes> searchClothesByUserAndKeyword(@Param("userId") String userId,
                                                @Param("keyword") String keyword,
                                                @Param("season") String season);




    /**
     * 分页查询搜索
     */
    @Select("""
<script>
SELECT id, description, image_url AS imageUrl, name, season
FROM clothes
WHERE user_id = #{userId}
  <if test="keyword != null and keyword != ''">
    AND (name LIKE CONCAT('%', #{keyword}, '%') OR description LIKE CONCAT('%', #{keyword}, '%'))
  </if>
  <if test="season != null and season != ''">
    AND season = #{season}
  </if>
ORDER BY update_time DESC
LIMIT #{pageSize} OFFSET #{offset}
</script>
""")
    List<Clothes> searchClothesByPage(@Param("userId") String userId,
                                      @Param("keyword") String keyword,
                                      @Param("season") String season,
                                      @Param("pageSize") int pageSize,
                                      @Param("offset") int offset);


    // 总数查询
    @Select("""
        <script>
        SELECT COUNT(*)
        FROM clothes
        WHERE user_id = #{userId}
          <if test="keyword != null and keyword != ''">
            AND (name LIKE CONCAT('%', #{keyword}, '%')
                 OR description LIKE CONCAT('%', #{keyword}, '%'))
          </if>
          <if test="season != null and season != ''">
            AND season = #{season}
          </if>
        </script>
    """)
    int countSearchClothes(@Param("userId") String userId,
                           @Param("keyword") String keyword,
                           @Param("season") String season);


    //新的get请求
    @Select("""
    <script>
    SELECT *
    FROM clothes
    WHERE user_id = #{userId}
      <if test="keyword != null and keyword != ''">
        AND (
            name LIKE CONCAT('%', #{keyword}, '%')
            OR description LIKE CONCAT('%', #{keyword}, '%')
        )
      </if>
      <if test="season != null and season != ''">
        AND season = #{season}
      </if>
      <if test="categoryList != null and categoryList.size > 0">
        AND category IN
        <foreach collection="categoryList" item="item" open="(" separator="," close=")">
          #{item}
        </foreach>
      </if>
    ORDER BY update_time DESC
    </script>
""")
    List<Clothes> searchClothesByEnhancedKeyword(@Param("userId") String userId,
                                                 @Param("keyword") String keyword,
                                                 @Param("season") String season,
                                                 @Param("categoryList") List<String> categoryList);


//    @Select("""
//<script>
//SELECT *
//FROM clothes
//WHERE 1=1
//  <if test="keyword != null and keyword != ''">
//    AND name LIKE CONCAT('%', #{keyword}, '%')
//  </if>
//  <if test="description != null and description != ''">
//    AND description LIKE CONCAT('%', #{description}, '%')
//  </if>
//  <if test="season != null and season != ''">
//    AND season LIKE CONCAT('%', #{season}, '%')
//  </if>
//ORDER BY update_time DESC
//LIMIT #{pageSize} OFFSET #{offset}
//</script>
//""")
//    List<Clothes> searchAllClothesByPage(@Param("keyword") String keyword,
//                                         @Param("description") String description,
//                                         @Param("season") String season,
//                                         @Param("pageSize") int pageSize,
//                                         @Param("offset") int offset);
//
//    @Select("""
//<script>
//SELECT COUNT(*)
//FROM clothes
//WHERE 1=1
//  <if test="keyword != null and keyword != ''">
//    AND name LIKE CONCAT('%', #{keyword}, '%')
//  </if>
//  <if test="description != null and description != ''">
//    AND description LIKE CONCAT('%', #{description}, '%')
//  </if>
//  <if test="season != null and season != ''">
//    AND season LIKE CONCAT('%', #{season}, '%')
//  </if>
//</script>
//""")
//    int countAllSearchClothes(@Param("keyword") String keyword,
//                              @Param("description") String description,
//                              @Param("season") String season);


    @Select("""
<script>
SELECT DISTINCT c.id, c.description, c.image_url AS imageUrl, c.name, c.season, c.update_time
FROM clothes c
LEFT JOIN categories ca ON c.category = ca.category
LEFT JOIN clothes_type ct ON c.category = ct.category
WHERE 1=1
  <if test="keyword != null and keyword != ''">
    AND (
      c.name LIKE CONCAT('%', #{keyword}, '%')
      OR ca.category_name LIKE CONCAT('%', #{keyword}, '%')
      OR ct.keywords LIKE CONCAT('%', #{keyword}, '%')
    )
  </if>
  <if test="description != null and description != ''">
    AND c.description LIKE CONCAT('%', #{description}, '%')
  </if>
  <if test="season != null and season != ''">
    AND c.season LIKE CONCAT('%', #{season}, '%')
  </if>
ORDER BY c.update_time DESC
LIMIT #{pageSize} OFFSET #{offset}
</script>
""")
    List<Clothes> searchAllClothesByPage(
            @Param("keyword") String keyword,
            @Param("description") String description,
            @Param("season") String season,
            @Param("pageSize") int pageSize,
            @Param("offset") int offset
    );

    @Select("""
<script>
SELECT COUNT(DISTINCT c.id)
FROM clothes c
LEFT JOIN categories ca ON c.category = ca.category
LEFT JOIN clothes_type ct ON c.category = ct.category
WHERE 1=1
  <if test="keyword != null and keyword != ''">
    AND (
      c.name LIKE CONCAT('%', #{keyword}, '%')
      OR ca.category_name LIKE CONCAT('%', #{keyword}, '%')
      OR ct.keywords LIKE CONCAT('%', #{keyword}, '%')
    )
  </if>
  <if test="description != null and description != ''">
    AND c.description LIKE CONCAT('%', #{description}, '%')
  </if>
  <if test="season != null and season != ''">
    AND c.season LIKE CONCAT('%', #{season}, '%')
  </if>
</script>
""")
    int countAllSearchClothes(
            @Param("keyword") String keyword,
            @Param("description") String description,
            @Param("season") String season
    );



    @Select("""
<script>
SELECT DISTINCT c.id, c.description, c.image_url AS imageUrl, c.name, c.season, c.update_time
FROM clothes c
LEFT JOIN clothes_type ct ON ct.category = c.category
LEFT JOIN categories ca ON ca.category = c.category
WHERE c.user_id = #{userId}
  <if test="keyword != null and keyword != ''">
    AND (
      c.name LIKE CONCAT('%', #{keyword}, '%')
      OR ct.keywords LIKE CONCAT('%', #{keyword}, '%')
      OR ca.category_name LIKE CONCAT('%', #{keyword}, '%')
    )
  </if>
  <if test="description != null and description != ''">
    AND c.description LIKE CONCAT('%', #{description}, '%')
  </if>
  <if test="season != null and season != ''">
    AND c.season LIKE CONCAT('%', #{season}, '%')
  </if>
ORDER BY c.update_time DESC
LIMIT #{pageSize} OFFSET #{offset}
</script>
""")
    List<Clothes> searchClothesByPages(
            @Param("userId") String userId,
            @Param("keyword") String keyword,
            @Param("description") String description,
            @Param("season") String season,
            @Param("pageSize") int pageSize,
            @Param("offset") int offset
    );

    @Select("""
<script>
SELECT COUNT(DISTINCT c.id)
FROM clothes c
LEFT JOIN clothes_type ct ON ct.category = c.category
LEFT JOIN categories ca ON ca.category = c.category
WHERE c.user_id = #{userId}
  <if test="keyword != null and keyword != ''">
    AND (
      c.name LIKE CONCAT('%', #{keyword}, '%') 
      OR ct.keywords LIKE CONCAT('%', #{keyword}, '%')
      OR ca.category_name LIKE CONCAT('%', #{keyword}, '%')
    )
  </if>
  <if test="description != null and description != ''">
    AND c.description LIKE CONCAT('%', #{description}, '%')
  </if>
  <if test="season != null and season != ''">
    AND c.season LIKE CONCAT('%', #{season}, '%')
  </if>
</script>
""")
    int countSearchClotheses(
            @Param("userId") String userId,
            @Param("keyword") String keyword,
            @Param("description") String description,
            @Param("season") String season
    );


}

